package com.cx.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author zhangshuai
 * @version V1.0
 * @Project Java2_26
 * @Package com.cx.jdbc
 * @Description:
 * @Date 2025/6/4 19:21
 */

public class JdbcTest1 {
    @Test
    public void  jdbcTest1() throws ClassNotFoundException, SQLException {

        //1. 加载驱动

            Class.forName("com.mysql.cj.jdbc.Driver");

        //2. 连接数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC",
                "root", "root");


        //3. 执行SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement("select * from users");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String city = resultSet.getString("city");
            String email = resultSet.getString("email");
            System.out.println(id+"\t"+name+"\t"+age+"\t"+city+"\t"+email);

        }
        connection.close();
        resultSet.close();
        preparedStatement.close();

        //4. 处理结果集
        //5. 关闭连接
        //6. 释放资源
    }

    @Test
    public void  jdbcTest2() throws ClassNotFoundException, SQLException {

        //1. 加载驱动

        Class.forName("com.mysql.cj.jdbc.Driver");

        //2. 连接数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC",
                "root", "root");


        //3. 执行SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement("insert into users (name,age,city,email,created_at) values (?,?,?,?,?)");
        preparedStatement.setString(1,"zhangsan");
        preparedStatement.setInt(2,18);
        preparedStatement.setString(3,"beijing");
        preparedStatement.setString(4,"zhangsan@qq.com");
        preparedStatement.setTimestamp(5,new Timestamp(System.currentTimeMillis()));
        int rows = preparedStatement.executeUpdate();
        if (rows>0){
            System.out.println("插入成功");
        }
        connection.close();

        preparedStatement.close();

        //4. 处理结果集
        //5. 关闭连接
        //6. 释放资源
    }

    @Test
    public void  jdbcTest3() throws Exception {

        //1. 加载驱动

        Class.forName("com.mysql.cj.jdbc.Driver");

        Properties properties = new Properties();

        InputStream resourceAsStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(resourceAsStream);


        //2. 连接数据库
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();

        //3. 执行SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement("select * from users");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String city = resultSet.getString("city");
            String email = resultSet.getString("email");
            System.out.println(id+"\t"+name+"\t"+age+"\t"+city+"\t"+email);

        }
        connection.close();
        resultSet.close();
        preparedStatement.close();

        //4. 处理结果集
        //5. 关闭连接
        //6. 释放资源
    }



}